package service

import (
	database "MyBlogv2/common/database"
	"MyBlogv2/common/helper"
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"io"
	"net/http"
	"strings"
	"sync"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/jmoiron/sqlx"
	ark "github.com/sashabaranov/go-openai"
)

type BackendAIAgent struct {
	client           *ark.Client
	model            string
	history          []ark.ChatCompletionMessage
	maxTokens        int
	sessionID        string
	db               *sqlx.DB
	tools            []ark.Tool
	pendingConfirmID string // 当前等待确认的 SQL 的 confirm_id
}

type BackendAgentRequest struct {
	UserMessage string `json:"user_message" binding:"required"`
	SessionID   string `json:"session_id"`
	Model       string `json:"model"`
	Level       int    `json:"level"`
	CurrentPage string `json:"current_page"`
}

type BackendAgentSession struct {
	ID        string                      `json:"id"`
	Name      string                      `json:"name"`
	History   []ark.ChatCompletionMessage `json:"history"`
	CreatedAt time.Time                   `json:"created_at"`
	UpdatedAt time.Time                   `json:"updated_at"`
}

type BackendAgentSessionRow struct {
	SessionID   string    `db:"session_id"`
	SessionName string    `db:"session_name"`
	Content     string    `db:"content"`
	CreatedAt   time.Time `db:"created_at"`
	UpdatedAt   time.Time `db:"updated_at"`
}

type BackendAgentSessionResponse struct {
	Session BackendAgentSession `json:"session"`
	Success bool                `json:"success"`
	Error   string              `json:"error,omitempty"`
}

type BackendAgentSessionListResponse struct {
	Sessions []BackendAgentSession `json:"sessions"`
	Success  bool                  `json:"success"`
	Error    string                `json:"error,omitempty"`
}

// PageFieldsConfig 页面字段配置
type PageFieldsConfig struct {
	TableName   string   // 主表名
	Fields      []string // 需要返回的字段
	Joins       []string // 需要的 JOIN 语句（可选）
	Description string   // 页面描述
	SampleQuery string   // 示例查询
}

// pageFieldsMap 页面字段映射表
var pageFieldsMap = map[string]PageFieldsConfig{
	"tag": {
		TableName: "t_tag",
		Fields: []string{
			"id",
			"tag_name AS tagName",
			"(SELECT COUNT(*) FROM t_article_tag WHERE tag_id = t_tag.id) AS articleCount",
			"created_at AS createTime",
			"updated_at AS updateTime",
		},
		Description: "标签管理页面 - 显示所有标签及其关联的文章数量",
		SampleQuery: `SELECT 
	id, 
	tag_name AS tagName,
	(SELECT COUNT(*) FROM t_article_tag WHERE tag_id = t_tag.id) AS articleCount,
	created_at AS createTime,
	updated_at AS updateTime
FROM t_tag
WHERE is_deleted = 0
ORDER BY created_at DESC`,
	},
	"comment": {
		TableName: "t_comment",
		Fields: []string{
			"c.id",
			"c.type",
			"c.type_id AS typeId",
			"c.parent_id AS parentId",
			"c.reply_id AS replyId",
			"c.comment_content AS commentContent",
			"u.username AS commentUserName",
			"c.is_check AS isCheck",
			"c.created_at AS createTime",
			"c.updated_at AS updateTime",
		},
		Description: "评论管理页面 - 显示所有评论及其审核状态",
		SampleQuery: `SELECT 
	c.id,
	c.type,
	c.type_id AS typeId,
	c.parent_id AS parentId,
	c.reply_id AS replyId,
	c.comment_content AS commentContent,
	u.username AS commentUserName,
	c.is_check AS isCheck,
	c.created_at AS createTime,
	c.updated_at AS updateTime
FROM t_comment c
LEFT JOIN sys_user u ON c.comment_user_id = u.id
ORDER BY c.created_at DESC`,
	},
	"category": {
		TableName: "t_category",
		Fields: []string{
			"id",
			"category_name AS categoryName",
			"(SELECT COUNT(*) FROM t_article WHERE category_id = t_category.id) AS articleCount",
			"created_at AS createTime",
			"updated_at AS updateTime",
		},
		Description: "分类管理页面 - 显示所有分类及其关联的文章数量",
		SampleQuery: `SELECT 
	id,
	category_name AS categoryName,
	(SELECT COUNT(*) FROM t_article WHERE category_id = t_category.id) AS articleCount,
	created_at AS createTime,
	updated_at AS updateTime
FROM t_category
ORDER BY created_at DESC`,
	},
	"user": {
		TableName: "sys_user",
		Fields: []string{
			"id",
			"username",
			"nickname",
			"avatar",
			"email",
			"register_type AS registerType",
			"login_address AS loginAddress",
			"is_disable AS isDisable",
			"created_at AS createTime",
		},
		Description: "用户管理页面 - 显示所有用户及其状态信息",
		SampleQuery: `SELECT 
	id,
	username,
	nickname,
	avatar,
	email,
	register_type AS registerType,
	login_address AS loginAddress,
	is_disable AS isDisable,
	created_at AS createTime
FROM sys_user
WHERE is_deleted = 0
ORDER BY created_at DESC`,
	},
	"role": {
		TableName: "sys_role",
		Fields: []string{
			"id",
			"role_name AS roleName",
			"role_key AS roleKey",
			"order_num AS orderNum",
			"status",
			"created_at AS createTime",
		},
		Description: "角色管理页面 - 显示所有角色及其状态",
		SampleQuery: `SELECT 
	id,
	role_name AS roleName,
	role_key AS roleKey,
	order_num AS orderNum,
	status,
	created_at AS createTime
FROM sys_role
WHERE is_deleted = 0
ORDER BY order_num ASC`,
	},
	"permission": {
		TableName: "sys_permission",
		Fields: []string{
			"p.id",
			"p.permission_key AS permissionKey",
			"p.permission_desc AS permissionDesc",
			"m.menu_name AS menuName",
			"p.menu_id AS menuId",
		},
		Description: "权限管理页面 - 显示所有权限及其关联的菜单",
		SampleQuery: `SELECT 
	p.id,
	p.permission_key AS permissionKey,
	p.permission_desc AS permissionDesc,
	m.menu_name AS menuName,
	p.menu_id AS menuId
FROM sys_permission p
LEFT JOIN sys_menu m ON p.menu_id = m.id
WHERE p.is_deleted = 0
ORDER BY p.id ASC`,
	},
}

// SQLConfirmation SQL 确认对象
type SQLConfirmation struct {
	SQL         string    `json:"sql"`         // 待执行的 SQL 语句
	Explanation string    `json:"explanation"` // SQL 说明（可选）
	Confirmed   bool      `json:"confirmed"`   // 是否已确认
	Rejected    bool      `json:"rejected"`    // 是否已拒绝
	Channel     chan bool `json:"-"`           // 确认通道（不序列化）
}

// ConfirmSQLRequest 用户确认请求
type ConfirmSQLRequest struct {
	ConfirmID string `json:"confirm_id" binding:"required"`
	Confirmed bool   `json:"confirmed"` // true=允许, false=拒绝
}

// 全局确认管理器（线程安全）
var sqlConfirmations = sync.Map{} // map[confirmID]*SQLConfirmation

func NewBackendAIAgentWithSession(apiKey, baseURL, model string, sessionID string) *BackendAIAgent {
	config := ark.DefaultConfig(apiKey)
	config.BaseURL = baseURL
	client := ark.NewClientWithConfig(config)

	history := []ark.ChatCompletionMessage{}

	tools := []ark.Tool{
		{
			Type: ark.ToolTypeFunction,
			Function: &ark.FunctionDefinition{
				Name:        "get_table_list",
				Description: "获取数据库中所有表的列表。在编写SQL前，应先调用此工具了解可用的表。",
				Parameters: map[string]interface{}{
					"type":       "object",
					"properties": map[string]interface{}{},
				},
			},
		},
		{
			Type: ark.ToolTypeFunction,
			Function: &ark.FunctionDefinition{
				Name:        "get_table_structure",
				Description: "获取指定表的结构信息，包括字段名、类型、注释等。",
				Parameters: map[string]interface{}{
					"type": "object",
					"properties": map[string]interface{}{
						"table_name": map[string]interface{}{
							"type":        "string",
							"description": "表名",
						},
					},
					"required": []string{"table_name"},
				},
			},
		},
		{
			Type: ark.ToolTypeFunction,
			Function: &ark.FunctionDefinition{
				Name:        "generate_sql",
				Description: "根据用户需求生成任何SQL语句（SELECT、INSERT、UPDATE、DELETE、CREATE、ALTER、DROP等）。必须先了解数据库结构。",
				Parameters: map[string]interface{}{
					"type": "object",
					"properties": map[string]interface{}{
						"sql": map[string]interface{}{
							"type":        "string",
							"description": "生成的SQL语句（支持所有类型）",
						},
						"explanation": map[string]interface{}{
							"type":        "string",
							"description": "SQL语句的简短说明，包括预期影响",
						},
					},
					"required": []string{"sql", "explanation"},
				},
			},
		},
		{
			Type: ark.ToolTypeFunction,
			Function: &ark.FunctionDefinition{
				Name:        "execute_sql",
				Description: "执行任何SQL语句并返回结果。支持所有SQL操作，但需要用户确认后才会执行。",
				Parameters: map[string]interface{}{
					"type": "object",
					"properties": map[string]interface{}{
						"sql": map[string]interface{}{
							"type":        "string",
							"description": "要执行的SQL语句（支持SELECT、INSERT、UPDATE、DELETE等所有SQL操作）",
						},
					},
					"required": []string{"sql"},
				},
			},
		},
		{
			Type: ark.ToolTypeFunction,
			Function: &ark.FunctionDefinition{
				Name:        "analyze_query_result",
				Description: "分析SQL查询结果并提供见解，包括统计摘要、趋势分析等。",
				Parameters: map[string]interface{}{
					"type": "object",
					"properties": map[string]interface{}{
						"analysis": map[string]interface{}{
							"type":        "string",
							"description": "对查询结果的分析和见解",
						},
					},
					"required": []string{"analysis"},
				},
			},
		},
	}

	if sessionID == "" {
		sessionID = generateSessionID()
	}

	return &BackendAIAgent{
		client:    client,
		model:     model,
		history:   history,
		maxTokens: 5000,
		sessionID: sessionID,
		db:        database.Db,
		tools:     tools,
	}
}

func BackendAgentChat(c *gin.Context) {
	var req BackendAgentRequest
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, ChatResponse{
			Success: false,
			Error:   err.Error(),
		})
		return
	}

	agent := NewBackendAIAgentWithSession(
		agent_config["content"][req.Model]["api_key"],
		agent_config["content"][req.Model]["base_url"],
		agent_config["content"][req.Model]["model"],
		req.SessionID,
	)

	if req.SessionID != "" {
		if err := agent.LoadBackendAgentSession(req.SessionID); err != nil {
			fmt.Printf("加载会话失败，使用新会话: %v\n", err)
		}
	}

	c.Header("Content-Type", "text/event-stream; charset=utf-8")
	c.Header("Cache-Control", "no-cache")
	c.Header("Connection", "keep-alive")
	c.Header("Access-Control-Allow-Origin", "*")
	c.Header("Access-Control-Allow-Headers", "Content-Type")
	c.Header("Access-Control-Allow-Methods", "POST, OPTIONS")

	if err := agent.ChatWithToolsStreamReal(req.UserMessage, c.Writer, req.CurrentPage); err != nil {
		c.JSON(http.StatusInternalServerError, ChatResponse{
			Success: false,
			Error:   err.Error(),
		})
		return
	}

	if err := agent.SaveBackendSession(); err != nil {
		fmt.Printf("警告: 自动保存会话失败: %v\n", err)
	}

	endData := fmt.Sprintf(`{"type":"end","session_id":"%s"}`, agent.sessionID)
	c.Writer.WriteString(fmt.Sprintf("data: %s\n\n", endData))
	c.Writer.WriteString("data: [DONE]\n\n")
	c.Writer.Flush()
}

func (agent *BackendAIAgent) ChatWithToolsStreamReal(userMessage string, writer gin.ResponseWriter, currentPage string) error {
	systemPrompt := `You are an expert database administrator and SQL operations assistant.
# Rules
  Always reply in Chinese

# Core Principles
1. Full SQL Support: Execute any SQL operations including SELECT, INSERT, UPDATE, DELETE, and DDL commands
2. Follow Standard Workflow: Always use the 4-step process
3. Clear Communication: Present results with insights and context
4. Error Recovery: Handle failures gracefully and provide alternatives
5. Automatic Execution: Execute validated SQL immediately without extra confirmation

# Available Tools

## Step 1: Database Exploration
- get_table_list: List all tables in database
  Purpose: Discover available tables before querying
  Usage: Call first when user asks about data
  
- get_table_structure(table_name): Show detailed table schema
  Purpose: Understand columns, types, keys, constraints
  Usage: Call after knowing table name, before writing SQL
  Returns: Column names, data types, keys, comments

## Step 2: SQL Generation
- generate_sql(sql, explanation): Create SQL statement
  Purpose: Generate any SQL statement based on requirements (SELECT, INSERT, UPDATE, DELETE, DDL, etc.)
  Usage: After exploring table structure
  Parameters:
    - sql: Any valid SQL statement
    - explanation: Brief description of what the SQL does
  Returns: Generated SQL with requires_confirmation flag

## Step 3: SQL Execution
- execute_sql(sql): Execute SQL statement directly
  Purpose: Execute any SQL statement once generated
  Usage: After generate_sql
  Limit: For SELECT queries, shows maximum 50 rows (indicates if more exist)

## Step 4: Result Analysis (Optional)
- analyze_query_result(analysis): Provide insights
  Purpose: Help user understand query results
  Usage: After execute_sql for complex queries
  Include: Trends, patterns, recommendations, statistics

# Standard 4-Step Workflow

## You MUST follow this sequence for ALL queries

### Step 1: Explore Database Structure
- Call get_table_list if tables are unknown
- Call get_table_structure(table_name) to understand columns

### Step 2: Generate SQL Query
- Call generate_sql with appropriate SELECT query
- Include clear explanation parameter

### Step 3: Execute Query
- Call execute_sql with the generated SQL
- Handle any execution errors

### Step 4: Analyze Results (Optional)
- For complex results, call analyze_query_result
- Provide meaningful insights to user

# Example Workflows

## Example 1: Simple Count Query
User: "How many users are there?"
AI Response:
1. get_table_structure("users") → verify table exists
2. generate_sql("SELECT COUNT(*) FROM users", "Count total users")
3. execute_sql("SELECT COUNT(*) FROM users")
4. Respond: "There are 1,234 users in the database"

## Example 2: Complex Join Query
User: "Show top 5 categories by article count"
AI Response:
1. get_table_list → verify tables exist
2. get_table_structure("article") → check article table
3. get_table_structure("category") → check category table
4. generate_sql(
     "SELECT c.name, COUNT(a.id) as count FROM category c 
      LEFT JOIN article a ON c.id = a.category_id 
      GROUP BY c.id ORDER BY count DESC LIMIT 5",
     "Get top 5 categories by article count"
   )
5. execute_sql([the SQL query])
6. analyze_query_result("Technology leads with 45 articles (35%)...")
7. Respond with formatted table and insights

## Example 3: Error Recovery
If execute_sql fails:
1. Read error message
2. Call get_table_structure again
3. Adjust SQL (fix column names, table names)
4. Retry with corrected query
5. Inform user what was fixed

# SQL Operations Support

## ALLOWED Operations
✓ SELECT queries: with WHERE, JOIN, GROUP BY, ORDER BY, LIMIT
✓ INSERT statements: single or multiple rows
✓ UPDATE statements: with WHERE conditions
✓ DELETE statements: with WHERE conditions (be careful!)
✓ DDL operations: CREATE, ALTER, DROP tables/indexes
✓ Aggregate functions: COUNT, SUM, AVG, MAX, MIN
✓ Subqueries and complex queries
✓ Transaction operations: BEGIN, COMMIT, ROLLBACK

## Important Safety Notes
- For DELETE/UPDATE: Always suggest showing affected rows with SELECT first
- For DROP operations: Warn user about permanent data loss
- Recommend backups before destructive operations
- Use transactions for multi-step modifications

# Error Handling

## Error: "Table doesn't exist"
- Call get_table_list to show available tables
- Suggest similar table names
- Response: "I couldn't find table 'xxx'. Available tables are: [list]. Did you mean 'yyy'?"

## Error: "Column not found"
- Call get_table_structure for the table
- Show available columns
- Response: "Column 'xxx' doesn't exist in this table. Available columns: [list]"

## Error: "Query timeout"
- Suggest adding WHERE clause to limit rows
- Recommend adding indexes (for user to implement)
- Response: "Query is taking too long. Try adding filters: WHERE created_at > '2024-01-01'"

## Error: "Too many rows"
- Automatically shown only first 50 rows
- Inform user about limitation
- Response: "Showing first 50 rows (total: 5,000). Consider adding filters or ORDER BY."

## Error: "User rejected SQL execution"
- When you receive "⚠️ 用户拒绝执行该 SQL 查询" from execute_sql tool
- DO NOT retry executing the same SQL query
- DO NOT call execute_sql again with the same or modified query
- Instead, politely ask the user if they want to modify the query
- Response template: "I understand you chose not to execute this query. Would you like me to:
  1. Modify the query (please specify what changes you'd like)
  2. Generate a different query for your needs
  3. Explain the query in more detail before execution"
- Wait for user's explicit instruction before proceeding
- Only after user provides new requirements, you can start the workflow again from Step 1 or Step 2

# Response Guidelines

## Communication Style
DO:
✓ After generate_sql: Immediately call execute_sql
✓ After execute_sql: Present results clearly - "Query executed successfully. 15 rows affected."
✓ Present results with insights: "Technology content dominates (35% of articles)"
✓ For modifications: Explain impact - "This will update 5 user records"
✓ For DELETE/DROP: Warn about permanent changes and suggest SELECT preview first

DON'T:
✗ Mention tool names: "I called get_table_list..." (never expose internal tools)
✗ Dump raw results: Always format and provide context
✗ Execute modifications without explaining consequences

## Result Formatting
- Use markdown tables for tabular data
- Highlight key numbers with context
- Add brief analysis after large result sets
- If > 50 rows, mention total count

## Example Response Format
"""
Query: SELECT category, COUNT(*) FROM articles GROUP BY category

Results:
| Category   | Count |
|------------|-------|
| Technology | 45    |
| Tutorial   | 38    |
| Blog       | 28    |

Analysis:
- Technology content dominates (35% of articles)
- Consider increasing Blog content to balance
- Total articles: 128
"""

# Best Practices
1. Always explore schema before querying (get_table_structure)
2. Use meaningful table/column aliases in complex queries
3. Add comments in generated SQL for clarity
4. Verify query results make sense (spot check)
5. For large tables, suggest LIMIT in initial queries
6. When joining, verify foreign key relationships exist
7. Present execution time for performance awareness

# Edge Cases

## Empty Results
If execute_sql returns 0 rows:
- Verify query is correct
- Suggest relaxing filters
- Response: "No results found. Try removing date filter or checking spelling"

## Modification Request (INSERT/UPDATE/DELETE)
If user asks to modify data:
- First, show what will be affected with a SELECT query
- Generate the modification SQL with clear explanation
- Execute once validated and communicate the impact
- Example: "I'll first show you the 5 records that will be deleted, then proceed with the deletion"

## Ambiguous Request
If unclear what data user wants:
- Call get_table_list first
- Ask specific clarifying question
- Suggest example: "Do you want user count by registration date or by status?"

## Performance Concerns
If query involves large tables:
- Suggest adding indexes (for admin to implement)
- Recommend LIMIT for testing
- Show execution time in response

Remember: You're a helpful assistant that prioritizes data safety and clear communication.`

	// 如果指定了 currentPage，添加页面特定的上下文
	if currentPage != "" {
		if pageConfig, exists := pageFieldsMap[currentPage]; exists {
			pageContext := fmt.Sprintf(`

# 当前页面上下文

用户正在查看 **%s**。

## 页面要求
- 主表: %s
- 必须返回的字段: %s
- 页面描述: %s

## 标准查询示例
%s

**重要提示**: 
1. 当用户查询与此页面相关的数据时，请确保 SQL 查询包含上述所有必需字段
2. 字段别名必须使用驼峰命名（如 tagName, createTime）以匹配前端要求
3. 如果用户需求不明确，使用上面的示例查询作为基础进行修改
4. 始终包含必要的过滤条件（如 is_deleted = 0）
5. 对于 tag 页面，articleCount 字段必须通过子查询从 t_article_tag 表计算得出`,
				pageConfig.Description,
				pageConfig.TableName,
				strings.Join(pageConfig.Fields, ", "),
				pageConfig.Description,
				pageConfig.SampleQuery,
			)
			systemPrompt += pageContext
		}
	}

	agent.history = append(agent.history, ark.ChatCompletionMessage{
		Role:    ark.ChatMessageRoleSystem,
		Content: systemPrompt,
	})

	agent.history = append(agent.history, ark.ChatCompletionMessage{
		Role:    ark.ChatMessageRoleUser,
		Content: userMessage,
	})

	ctx := context.Background()
	maxIterations := 10
	iteration := 0

	for iteration < maxIterations {
		iteration++

		req := ark.ChatCompletionRequest{
			Model:    agent.model,
			Messages: agent.history,
			Tools:    agent.tools,
			Stream:   true,
		}

		stream, err := agent.client.CreateChatCompletionStream(ctx, req)
		if err != nil {
			return fmt.Errorf("AI流式请求失败: %v", err)
		}

		var fullContent strings.Builder
		var fullReasoning strings.Builder
		var toolCalls []ark.ToolCall
		var currentBubbleID string
		var thinkingBubbleID string
		var thinkingStarted bool
		toolBubbleIDs := make(map[int]string)

		for {
			response, err := stream.Recv()
			if err == io.EOF {
				break
			}
			if err != nil {
				stream.Close()
				return fmt.Errorf("接收流式响应失败: %v", err)
			}

			if len(response.Choices) == 0 {
				continue
			}

			delta := response.Choices[0].Delta

			if delta.ReasoningContent != "" {
				fullReasoning.WriteString(delta.ReasoningContent)

				if !thinkingStarted {
					thinkingBubbleID = fmt.Sprintf("thinking_%d", time.Now().UnixNano())
					thinkingStartData := map[string]interface{}{
						"type":       "thinking_start",
						"session_id": agent.sessionID,
						"bubble_id":  thinkingBubbleID,
					}
					jsonData, _ := json.Marshal(thinkingStartData)
					writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
					writer.Flush()
					thinkingStarted = true
				}

				thinkingData := map[string]interface{}{
					"type":       "thinking",
					"content":    delta.ReasoningContent,
					"session_id": agent.sessionID,
					"bubble_id":  thinkingBubbleID,
				}
				jsonData, _ := json.Marshal(thinkingData)
				writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
				writer.Flush()
			}

			if delta.Content != "" {
				if thinkingStarted && thinkingBubbleID != "" {
					thinkingEndData := map[string]interface{}{
						"type":       "thinking_end",
						"session_id": agent.sessionID,
						"bubble_id":  thinkingBubbleID,
					}
					jsonData, _ := json.Marshal(thinkingEndData)
					writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
					writer.Flush()
					thinkingBubbleID = ""
					thinkingStarted = false
				}

				fullContent.WriteString(delta.Content)

				if currentBubbleID == "" {
					currentBubbleID = fmt.Sprintf("response_%d", time.Now().UnixNano())
				}

				contentData := map[string]interface{}{
					"type":       "content",
					"content":    delta.Content,
					"session_id": agent.sessionID,
					"bubble_id":  currentBubbleID,
				}
				jsonData, _ := json.Marshal(contentData)
				writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
				writer.Flush()
			}

			if len(delta.ToolCalls) > 0 {
				if thinkingStarted && thinkingBubbleID != "" {
					thinkingEndData := map[string]interface{}{
						"type":       "thinking_end",
						"session_id": agent.sessionID,
						"bubble_id":  thinkingBubbleID,
					}
					jsonData, _ := json.Marshal(thinkingEndData)
					writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
					writer.Flush()
					thinkingBubbleID = ""
					thinkingStarted = false
				}

				for _, toolCallDelta := range delta.ToolCalls {
					index := *toolCallDelta.Index

					for len(toolCalls) <= index {
						toolCalls = append(toolCalls, ark.ToolCall{})
					}

					if toolCallDelta.ID != "" {
						toolCalls[index].ID = toolCallDelta.ID
					}
					if toolCallDelta.Type != "" {
						toolCalls[index].Type = toolCallDelta.Type
					}
					if toolCallDelta.Function.Name != "" {
						toolCalls[index].Function.Name = toolCallDelta.Function.Name

						if _, exists := toolBubbleIDs[index]; !exists {
							toolBubbleIDs[index] = fmt.Sprintf("tool_%s_%d", toolCallDelta.Function.Name, time.Now().UnixNano())
						}

						toolStartData := map[string]interface{}{
							"type":       "tool_start",
							"tool_name":  toolCallDelta.Function.Name,
							"session_id": agent.sessionID,
							"bubble_id":  toolBubbleIDs[index],
						}
						jsonData, _ := json.Marshal(toolStartData)
						writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
						writer.Flush()
					}
					if toolCallDelta.Function.Arguments != "" {
						toolCalls[index].Function.Arguments += toolCallDelta.Function.Arguments
					}
				}
			}
		}

		stream.Close()

		if thinkingStarted && thinkingBubbleID != "" {
			thinkingEndData := map[string]interface{}{
				"type":       "thinking_end",
				"session_id": agent.sessionID,
				"bubble_id":  thinkingBubbleID,
			}
			jsonData, _ := json.Marshal(thinkingEndData)
			writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
			writer.Flush()
		}

		assistantMessage := ark.ChatCompletionMessage{
			Role:             ark.ChatMessageRoleAssistant,
			ReasoningContent: fullReasoning.String(),
			Content:          fullContent.String(),
		}

		if len(toolCalls) > 0 {
			assistantMessage.ToolCalls = toolCalls
			agent.history = append(agent.history, assistantMessage)

			for index, toolCall := range toolCalls {
				bubbleID := toolBubbleIDs[index]

				// ===== 【新增】execute_sql 特殊处理 - 发送确认请求 =====
				if toolCall.Function.Name == "execute_sql" {
					var params map[string]interface{}
					if err := json.Unmarshal([]byte(toolCall.Function.Arguments), &params); err == nil {
						if sqlQuery, ok := params["sql"].(string); ok {
							// 生成确认 ID（与 ExecuteSQL 中一致）
							confirmID := fmt.Sprintf("%s_%d", agent.sessionID, time.Now().UnixNano())

							// 【关键修复】将 confirmID 存储到 agent，供 ExecuteSQL 使用
							agent.pendingConfirmID = confirmID

							// 发送确认请求到前端
							confirmRequestData := map[string]interface{}{
								"type":       "sql_confirm_request",
								"confirm_id": confirmID,
								"session_id": agent.sessionID,
								"sql":        sqlQuery,
								"bubble_id":  bubbleID,
								"tool_name":  "execute_sql",
							}
							jsonData, _ := json.Marshal(confirmRequestData)
							writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
							writer.Flush()
						}
					}
				}

				// 执行工具（内部会等待用户确认）
				toolResult, err := agent.ExecuteBackendTool(toolCall)
				if err != nil {
					toolResult = fmt.Sprintf("工具执行失败: %v", err)
				}

				agent.streamToolResult(toolResult, writer, bubbleID, toolCall.Function.Name)

				if toolResult == "" {
					toolResult = " "
				}

				agent.history = append(agent.history, ark.ChatCompletionMessage{
					Role:       ark.ChatMessageRoleTool,
					Content:    toolResult,
					ToolCallID: toolCall.ID,
					Name:       toolCall.Function.Name,
				})

				toolEndData := map[string]interface{}{
					"type":        "tool_end",
					"tool_name":   toolCall.Function.Name,
					"session_id":  agent.sessionID,
					"bubble_id":   bubbleID,
					"tool_result": "Success",
				}
				jsonData, _ := json.Marshal(toolEndData)
				writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
				writer.Flush()
			}

			continue
		} else {
			agent.history = append(agent.history, assistantMessage)
			break
		}
	}

	if iteration >= maxIterations {
		errorData := map[string]interface{}{
			"type":    "error",
			"content": "达到最大迭代次数，对话终止",
		}
		jsonData, _ := json.Marshal(errorData)
		writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
		writer.Flush()
	}

	agent.history = clearBackendSystemMessage(agent.history)
	return nil
}

func (agent *BackendAIAgent) ExecuteBackendTool(toolCall ark.ToolCall) (string, error) {
	if toolCall.Function.Name == "" {
		return "", fmt.Errorf("工具调用缺少函数信息")
	}

	functionName := toolCall.Function.Name
	arguments := toolCall.Function.Arguments

	var params map[string]interface{}
	if arguments != "" {
		if err := json.Unmarshal([]byte(arguments), &params); err != nil {
			return "", fmt.Errorf("解析工具参数失败: %v", err)
		}
	}

	switch functionName {
	case "get_table_list":
		return agent.GetTableList()
	case "get_table_structure":
		tableName, _ := params["table_name"].(string)
		return agent.GetTableStructure(tableName)
	case "generate_sql":
		sqlQuery, _ := params["sql"].(string)
		explanation, _ := params["explanation"].(string)
		return agent.GenerateSQL(sqlQuery, explanation)
	case "execute_sql":
		sqlQuery, _ := params["sql"].(string)
		return agent.ExecuteSQL(sqlQuery)
	case "analyze_query_result":
		analysis, _ := params["analysis"].(string)
		return agent.AnalyzeQueryResult(analysis)
	default:
		return "", fmt.Errorf("未知的工具函数: %s", functionName)
	}
}

func (agent *BackendAIAgent) GetTableList() (string, error) {
	var tables []string
	rows, err := agent.db.Query("SHOW TABLES")
	if err != nil {
		return "", fmt.Errorf("获取表列表失败: %v", err)
	}
	defer rows.Close()

	for rows.Next() {
		var tableName string
		if err := rows.Scan(&tableName); err != nil {
			continue
		}
		tables = append(tables, tableName)
	}

	if len(tables) == 0 {
		return "数据库中暂无表", nil
	}

	result := fmt.Sprintf("数据库中共有 %d 个表：\n\n", len(tables))
	for i, table := range tables {
		result += fmt.Sprintf("%d. %s\n", i+1, table)
	}

	return result, nil
}

func (agent *BackendAIAgent) GetTableStructure(tableName string) (string, error) {
	if tableName == "" {
		return "", fmt.Errorf("表名不能为空")
	}

	if strings.Contains(tableName, " ") || strings.Contains(tableName, ";") {
		return "", fmt.Errorf("无效的表名")
	}

	type ColumnInfo struct {
		Field      string         `db:"Field"`
		Type       string         `db:"Type"`
		Collation  sql.NullString `db:"Collation"`
		Null       string         `db:"Null"`
		Key        string         `db:"Key"`
		Default    sql.NullString `db:"Default"`
		Extra      string         `db:"Extra"`
		Privileges string         `db:"Privileges"`
		Comment    string         `db:"Comment"`
	}

	var columns []ColumnInfo
	query := fmt.Sprintf("SHOW FULL COLUMNS FROM `%s`", tableName)
	if err := agent.db.Select(&columns, query); err != nil {
		return "", fmt.Errorf("获取表结构失败: %v", err)
	}

	if len(columns) == 0 {
		return fmt.Sprintf("表 %s 不存在或无字段", tableName), nil
	}

	result := fmt.Sprintf("表 `%s` 的结构：\n\n", tableName)
	result += "| 字段名 | 类型 | 允许NULL | 键 | 默认值 | 额外 | 注释 |\n"
	result += "|--------|------|----------|-----|--------|------|------|\n"

	for _, col := range columns {
		defaultVal := "NULL"
		if col.Default.Valid {
			defaultVal = col.Default.String
		}
		result += fmt.Sprintf("| %s | %s | %s | %s | %s | %s | %s |\n",
			col.Field, col.Type, col.Null, col.Key, defaultVal, col.Extra, col.Comment)
	}

	return result, nil
}

func (agent *BackendAIAgent) GenerateSQL(sqlQuery, explanation string) (string, error) {
	respMap := map[string]interface{}{
		"success":               true,
		"sql":                   sqlQuery,
		"explanation":           explanation,
		"requires_confirmation": true,
		"confirmation_message":  "请确认是否执行此SQL查询",
	}
	jsonData, err := json.Marshal(respMap)
	if err != nil {
		return "", fmt.Errorf("JSON序列化失败: %v", err)
	}
	return string(jsonData), nil
}

func (agent *BackendAIAgent) ExecuteSQL(sqlQuery string) (string, error) {
	sqlQuery = strings.TrimSpace(sqlQuery)

	// ===== 【关键新增】用户确认机制 =====

	// 1. 使用之前存储的确认 ID（由 ChatWithToolsStreamReal 生成）
	confirmID := agent.pendingConfirmID
	if confirmID == "" {
		// 降级方案：如果没有预存的 ID，则生成一个（不应该发生）
		confirmID = fmt.Sprintf("%s_%d", agent.sessionID, time.Now().UnixNano())
	}

	// 2. 创建确认对象
	confirmation := &SQLConfirmation{
		SQL:     sqlQuery,
		Channel: make(chan bool, 1),
	}
	sqlConfirmations.Store(confirmID, confirmation)

	// 3. 等待用户确认（带超时）
	select {
	case confirmed := <-confirmation.Channel:
		sqlConfirmations.Delete(confirmID) // 清理
		agent.pendingConfirmID = ""        // 【关键修复】清理 confirmID，防止下次误用
		if !confirmed {
			return "⚠️ 用户拒绝执行该 SQL 查询", nil
		}
		// 用户确认，继续执行...
	case <-time.After(5 * time.Minute):
		sqlConfirmations.Delete(confirmID)
		agent.pendingConfirmID = "" // 【关键修复】清理 confirmID
		return "", fmt.Errorf("⏱️ 等待用户确认超时（5分钟）")
	}

	// ===== 原有的 SQL 执行逻辑 =====

	startTime := time.Now()
	sqlLower := strings.ToLower(sqlQuery)

	// 判断是查询语句还是修改语句
	if strings.HasPrefix(sqlLower, "select") || strings.HasPrefix(sqlLower, "show") || strings.HasPrefix(sqlLower, "describe") || strings.HasPrefix(sqlLower, "desc") {
		// 查询语句，返回结果集
		rows, err := agent.db.Query(sqlQuery)
		if err != nil {
			return "", fmt.Errorf("查询失败: %v", err)
		}
		defer rows.Close()

		columns, err := rows.Columns()
		if err != nil {
			return "", fmt.Errorf("获取列信息失败: %v", err)
		}

		var result []map[string]interface{}
		for rows.Next() {
			values := make([]interface{}, len(columns))
			valuePtrs := make([]interface{}, len(columns))
			for i := range values {
				valuePtrs[i] = &values[i]
			}

			if err := rows.Scan(valuePtrs...); err != nil {
				return "", fmt.Errorf("读取数据失败: %v", err)
			}

			row := make(map[string]interface{})
			for i, col := range columns {
				val := values[i]
				if b, ok := val.([]byte); ok {
					row[col] = string(b)
				} else {
					row[col] = val
				}
			}
			result = append(result, row)
		}

		executeTime := time.Since(startTime).Milliseconds()
		resultText := fmt.Sprintf("✅ 查询成功！共 %d 行数据，执行时间: %d ms\n\n", len(result), executeTime)

		if len(result) == 0 {
			resultText += "查询结果为空。"
		} else {
			resultText += "| " + strings.Join(columns, " | ") + " |\n"
			resultText += "|" + strings.Repeat("------|", len(columns)) + "\n"

			maxRows := 50
			displayRows := result
			if len(result) > maxRows {
				displayRows = result[:maxRows]
				resultText += fmt.Sprintf("（显示前 %d 行，共 %d 行）\n\n", maxRows, len(result))
			}

			for _, row := range displayRows {
				rowValues := make([]string, len(columns))
				for i, col := range columns {
					val := row[col]
					if val == nil {
						rowValues[i] = "NULL"
					} else {
						rowValues[i] = fmt.Sprintf("%v", val)
					}
				}
				resultText += "| " + strings.Join(rowValues, " | ") + " |\n"
			}
		}

		return resultText, nil
	} else {
		// 修改语句（INSERT, UPDATE, DELETE, CREATE, ALTER, DROP 等）
		result, err := agent.db.Exec(sqlQuery)
		if err != nil {
			return "", fmt.Errorf("执行失败: %v", err)
		}

		executeTime := time.Since(startTime).Milliseconds()

		// 获取影响的行数
		rowsAffected, err := result.RowsAffected()
		if err != nil {
			return fmt.Sprintf("✅ SQL 执行成功！执行时间: %d ms", executeTime), nil
		}

		// 根据 SQL 类型返回不同的消息
		var operation string
		if strings.HasPrefix(sqlLower, "insert") {
			operation = "插入"
		} else if strings.HasPrefix(sqlLower, "update") {
			operation = "更新"
		} else if strings.HasPrefix(sqlLower, "delete") {
			operation = "删除"
		} else if strings.HasPrefix(sqlLower, "create") {
			operation = "创建"
		} else if strings.HasPrefix(sqlLower, "alter") {
			operation = "修改"
		} else if strings.HasPrefix(sqlLower, "drop") {
			operation = "删除"
		} else {
			operation = "执行"
		}

		return fmt.Sprintf("✅ %s成功！影响行数: %d，执行时间: %d ms", operation, rowsAffected, executeTime), nil
	}
}

func containsDangerousKeyword(sqlLower, keyword string) bool {
	separators := " \n\r\t,();=><!*+-/`\"'[]{}"

	isSeparator := func(b byte) bool {
		return strings.ContainsRune(separators, rune(b))
	}

	index := strings.Index(sqlLower, keyword)
	for index != -1 {
		beforeOK := index == 0 || isSeparator(sqlLower[index-1])
		afterPos := index + len(keyword)
		afterOK := afterPos >= len(sqlLower) || isSeparator(sqlLower[afterPos])

		if beforeOK && afterOK {
			return true
		}

		nextStart := index + len(keyword)
		if nextStart >= len(sqlLower) {
			break
		}
		nextIndex := strings.Index(sqlLower[nextStart:], keyword)
		if nextIndex == -1 {
			break
		}
		index = nextStart + nextIndex
	}

	return false
}

func (agent *BackendAIAgent) AnalyzeQueryResult(analysis string) (string, error) {
	return analysis, nil
}

func (agent *BackendAIAgent) streamToolResult(result string, writer gin.ResponseWriter, bubbleID string, functionName string) {
	chunkSize := 30
	runes := []rune(result)

	for i := 0; i < len(runes); i += chunkSize {
		end := i + chunkSize
		if end > len(runes) {
			end = len(runes)
		}

		chunk := string(runes[i:end])

		toolResultData := map[string]interface{}{
			"type":       "tool_result",
			"content":    chunk,
			"session_id": agent.sessionID,
			"bubble_id":  bubbleID,
			"name":       functionName,
		}

		jsonData, err := json.Marshal(toolResultData)
		if err != nil {
			escapedContent := strings.ReplaceAll(chunk, `\`, `\\`)
			escapedContent = strings.ReplaceAll(escapedContent, `"`, `\"`)
			escapedContent = strings.ReplaceAll(escapedContent, "\n", `\n`)
			jsonData = []byte(fmt.Sprintf(`{"type":"tool_result","content":"%s","session_id":"%s","bubble_id":"%s","name":"%s"}`, escapedContent, agent.sessionID, bubbleID, functionName))
		}

		writer.WriteString(fmt.Sprintf("data: %s\n\n", string(jsonData)))
		writer.Flush()
		time.Sleep(30 * time.Millisecond)
	}
}

func (agent *BackendAIAgent) LoadBackendAgentSession(sessionID string) error {
	var contentJSON string
	err := agent.db.Get(&contentJSON, "SELECT content FROM backend_agent_sessions WHERE session_id = ?", sessionID)
	if err != nil {
		if err == sql.ErrNoRows {
			return fmt.Errorf("会话不存在: %s", sessionID)
		}
		return fmt.Errorf("从数据库加载会话失败: %v", err)
	}

	var session BackendAgentSession
	if err := json.Unmarshal([]byte(contentJSON), &session); err != nil {
		return fmt.Errorf("解码会话数据失败: %v", err)
	}

	agent.history = session.History
	agent.sessionID = session.ID
	return nil
}

func (agent *BackendAIAgent) SaveBackendSession() error {
	sessionName := "新建查询"
	if len(agent.history) > 1 {
		for _, msg := range agent.history[1:] {
			if msg.Role == ark.ChatMessageRoleUser {
				sessionName = msg.Content
				if len([]rune(sessionName)) > 50 {
					sessionName = string([]rune(sessionName)[:50]) + "..."
				}
				break
			}
		}
	}

	sessionName = strings.ToValidUTF8(sessionName, "?")

	session := BackendAgentSession{
		ID:        agent.sessionID,
		Name:      sessionName,
		History:   agent.history,
		CreatedAt: time.Now(),
		UpdatedAt: time.Now(),
	}

	sessionJSON, err := json.Marshal(session)
	if err != nil {
		return fmt.Errorf("编码会话数据失败: %v", err)
	}

	jsonStr := strings.ToValidUTF8(string(sessionJSON), "?")

	var count int
	err = agent.db.Get(&count, "SELECT COUNT(*) FROM backend_agent_sessions WHERE session_id = ?", agent.sessionID)
	if err != nil {
		return fmt.Errorf("检查会话存在性失败: %v", err)
	}

	currentTime := helper.GetCurrentTimeString()

	if count > 0 {
		_, err = agent.db.Exec(`
			UPDATE backend_agent_sessions 
			SET session_name = ?, content = ?, updated_at = ? 
			WHERE session_id = ?
		`, sessionName, jsonStr, currentTime, agent.sessionID)
	} else {
		_, err = agent.db.Exec(`
			INSERT INTO backend_agent_sessions (session_id, session_name, content, created_at, updated_at) 
			VALUES (?, ?, ?, ?, ?)
		`, agent.sessionID, sessionName, jsonStr, currentTime, currentTime)
	}

	if err != nil {
		return fmt.Errorf("保存会话到数据库失败: %v", err)
	}

	return nil
}

func CreateBackendAgentSession(c *gin.Context) {
	agent := NewBackendAIAgentWithSession(
		agent_config["content"]["gemini_flash"]["api_key"],
		agent_config["content"]["gemini_flash"]["base_url"],
		agent_config["content"]["gemini_flash"]["model"],
		"",
	)

	if err := agent.SaveBackendSession(); err != nil {
		c.JSON(http.StatusInternalServerError, BackendAgentSessionResponse{
			Success: false,
			Error:   err.Error(),
		})
		return
	}

	session := BackendAgentSession{
		ID:        agent.sessionID,
		Name:      "新建查询",
		History:   agent.history,
		CreatedAt: time.Now(),
		UpdatedAt: time.Now(),
	}

	c.JSON(http.StatusOK, BackendAgentSessionResponse{
		Session: session,
		Success: true,
	})
}

func GetBackendAgentSessions(c *gin.Context) {
	sessions, err := ListBackendAgentSessions()
	if err != nil {
		c.JSON(http.StatusInternalServerError, BackendAgentSessionListResponse{
			Success: false,
			Error:   err.Error(),
		})
		return
	}

	c.JSON(http.StatusOK, BackendAgentSessionListResponse{
		Sessions: sessions,
		Success:  true,
	})
}

func ListBackendAgentSessions() ([]BackendAgentSession, error) {
	var rows []BackendAgentSessionRow
	err := database.Db.Select(&rows, `
		SELECT session_id, session_name, content, created_at, updated_at 
		FROM backend_agent_sessions 
		ORDER BY updated_at DESC
	`)
	if err != nil {
		return nil, fmt.Errorf("查询会话列表失败: %v", err)
	}

	var sessions []BackendAgentSession
	for _, row := range rows {
		var session BackendAgentSession
		if err := json.Unmarshal([]byte(row.Content), &session); err != nil {
			continue
		}

		session.CreatedAt = row.CreatedAt
		session.UpdatedAt = row.UpdatedAt
		sessions = append(sessions, session)
	}

	return sessions, nil
}

func LoadBackendAgentSession(c *gin.Context) {
	sessionID := c.Param("id")

	agent := NewBackendAIAgentWithSession(
		agent_config["content"]["gemini_flash"]["api_key"],
		agent_config["content"]["gemini_flash"]["base_url"],
		agent_config["content"]["gemini_flash"]["model"],
		sessionID,
	)

	if err := agent.LoadBackendAgentSession(sessionID); err != nil {
		c.JSON(http.StatusNotFound, BackendAgentSessionResponse{
			Success: false,
			Error:   err.Error(),
		})
		return
	}

	session := BackendAgentSession{
		ID:        agent.sessionID,
		Name:      getSessionName(agent.history),
		History:   agent.history,
		CreatedAt: time.Now(),
		UpdatedAt: time.Now(),
	}

	c.JSON(http.StatusOK, BackendAgentSessionResponse{
		Session: session,
		Success: true,
	})
}

func DeleteBackendAgentSession(c *gin.Context) {
	sessionID := c.Param("id")

	if err := deleteBackendAgentSession(sessionID); err != nil {
		c.JSON(http.StatusInternalServerError, gin.H{
			"success": false,
			"error":   err.Error(),
		})
		return
	}

	c.JSON(http.StatusOK, gin.H{
		"success": true,
		"message": "会话删除成功",
	})
}

func deleteBackendAgentSession(sessionID string) error {
	result, err := database.Db.Exec("DELETE FROM backend_agent_sessions WHERE session_id = ?", sessionID)
	if err != nil {
		return fmt.Errorf("删除会话失败: %v", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("获取删除结果失败: %v", err)
	}

	if rowsAffected == 0 {
		return fmt.Errorf("会话不存在: %s", sessionID)
	}

	return nil
}

// ConfirmSQL 处理用户 SQL 确认请求
func ConfirmSQL(c *gin.Context) {
	var req ConfirmSQLRequest
	if err := c.ShouldBindJSON(&req); err != nil {
		c.JSON(http.StatusBadRequest, gin.H{
			"success": false,
			"error":   "参数错误: " + err.Error(),
		})
		return
	}

	// 查找对应的确认对象
	value, exists := sqlConfirmations.Load(req.ConfirmID)
	if !exists {
		c.JSON(http.StatusNotFound, gin.H{
			"success": false,
			"error":   "确认请求不存在或已过期",
		})
		return
	}

	confirmation := value.(*SQLConfirmation)

	// 发送确认结果到等待的 goroutine
	select {
	case confirmation.Channel <- req.Confirmed:
		c.JSON(http.StatusOK, gin.H{
			"success": true,
			"message": "确认结果已提交",
			"action":  map[bool]string{true: "允许执行", false: "拒绝执行"}[req.Confirmed],
		})
	default:
		// 通道已关闭或已有结果
		c.JSON(http.StatusBadRequest, gin.H{
			"success": false,
			"error":   "无法提交确认结果（可能已超时）",
		})
	}
}

func clearBackendSystemMessage(history []ark.ChatCompletionMessage) []ark.ChatCompletionMessage {
	j := 0
	for _, msg := range history {
		if msg.Role == ark.ChatMessageRoleSystem {
			continue
		}
		history[j] = msg
		j++
	}
	return history[:j]
}
